/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.kallsonys.dao;

import com.kallsonys.service.Address;
import com.kallsonys.service.CreditCard;
import com.kallsonys.service.Customer;
import com.kallsonys.service.ElementListPage;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author allan
 */
public class CustomerDAO {

    private static String CREATE_CUSTOMER = "{ call PKG_CURDS.CREATE_CUSTOMER (?, ?, ?, ?, ?, ?, ?) }";
    private static String UPDATE_CUSTOMER = "{ call PKG_CURDS.UPDATE_CUSTOMER (?, ?, ?, ?, ?, ?, ?, ?, ?) }";
    private static String GET_CUSTOMERS = "{ call PKG_CURDS.GET_CUSTOMERS (?, ?, ?) }";
    private static String GET_CUSTOMER_DOCUMENT = "{ call PKG_CURDS.GET_CUSTOMER_DOCUMENT (?, ?) }";
    private static String GET_CUSTOMER_MAIL = "{ call PKG_CURDS.GET_CUSTOMER_MAIL (?, ?) }";
    private static String GET_CUSTOMER = "{ call PKG_CURDS.GET_CUSTOMER (?, ?) }";
    private static String CREATE_ADDRESS = "{ call PKG_CURDS.CREATE_ADDRESS (?, ?, ?, ?, ?, ?, ?) }";
    private static String UPDATE_ADDRESS = "{ call PKG_CURDS.UPDATE_ADDRESS (?, ?, ?, ?, ?, ?, ?) }";
    private static String GET_ADDRESS_CUSTOMER = "{ call PKG_CURDS.GET_ADDRESS_CUSTOMER (?, ?) }";
    private static String CREATE_CREDITCARD = "{ call PKG_CURDS.CREATE_CREDITCARD (?, ?, ?, ?, ?, ?, ?, ?) }";
    private static String UPDATE_CREDITCARD = "{ call PKG_CURDS.UPDATE_CREDITCARD (?, ?, ?, ?, ?, ?, ?) }";
    private static String GET_CREDITCARD_CUSTOMER = "{ call PKG_CURDS.GET_CREDITCARD_CUSTOMER (?, ?) }";
    private static String GET_CREDITCARD = "{ call PKG_CURDS.GET_CREDITCARD(?, ?) }";
    private static String GET_CUSTOMERS_BY_PRODUCT = "{ call PKG_CURDS.GET_CUSTOMERS_BY_PRODUCT(?, ?, ?, ?, ?) }";
    private static String GET_CUSTOMERS_BY_BILLING = "{ call PKG_CURDS.GET_CUSTOMERS_BY_BILLING(?, ?, ?, ?, ?, ?) }";
    private DataSource ds;

    public CustomerDAO(DataSource ds) {
        this.ds = ds;
    }

    public long CreateCustomer(Customer customer) throws Exception {
        long retorno = 0;
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(CREATE_CUSTOMER);

            stmt.setString(1, customer.getFirstName());
            stmt.setString(2, customer.getLastName());
            stmt.setString(3, customer.getEmail());
            stmt.setString(4, customer.getPassword());
            stmt.setInt(5, customer.getType());
            stmt.setString(6, customer.getDocument());
            stmt.registerOutParameter(7, OracleTypes.INTEGER);
            stmt.execute();

            retorno = stmt.getLong(7);

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public void UpdateCustomer(Customer customer) throws Exception {

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(UPDATE_CUSTOMER);

            stmt.setLong(1, customer.getId());
            stmt.setString(2, customer.getFirstName());
            stmt.setString(3, customer.getLastName());
            stmt.setString(4, customer.getEmail());
            stmt.setString(5, customer.getPassword());
            stmt.setInt(6, customer.getType());
            stmt.setString(7, customer.getDocument());
            stmt.setLong(8, customer.getLastUserCreditCardId());
            stmt.setLong(9, customer.getLastUserAddressId());
            stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return;
    }

    public ElementListPage<Customer> getAllCustomer(int page) throws Exception {
        ElementListPage<Customer> customerList = new ElementListPage<Customer>();
        customerList.setElementList(new ArrayList<Customer>());

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_CUSTOMERS);


            stmt.setInt(1, page);
            stmt.registerOutParameter(2, OracleTypes.INTEGER);
            stmt.registerOutParameter(3, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(3);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Customer retorno = new Customer();
                    retorno.setId(rs.getLong("CUSTOMERID"));
                    retorno.setDocument(rs.getString("CUSTOMERDOCUMENT"));
                    retorno.setEmail(rs.getString("CUSTOMEREMAIL"));
                    retorno.setFirstName(rs.getString("CUSTOMERFIRSTNAME"));
                    retorno.setLastName(rs.getString("CUSTOMERLASTNAME"));
                    retorno.setLastUserCreditCardId(rs.getLong("LASTUSEDCREDITCARDID"));
                    retorno.setLastUserAddressId(rs.getLong("LASTUSEDADDRESSID"));
                    retorno.setPassword("");
                    retorno.setType(rs.getInt("CUSTOMERTYPE"));

                    customerList.getElementList().add(retorno);
                }

                customerList.setPages(stmt.getInt(2));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return customerList;
    }

    public Customer getByCustomer(String document, int query) throws Exception {
        Customer retorno = null;

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            if (query == 1) {
                stmt = con.prepareCall(GET_CUSTOMER_DOCUMENT);
            }
            if (query == 2) {
                stmt = con.prepareCall(GET_CUSTOMER_MAIL);
            }
            stmt.setString(1, document.trim());
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                if (rs.next()) {
                    retorno = new Customer();
                    retorno.setId(rs.getLong("CUSTOMERID"));
                    retorno.setDocument(rs.getString("CUSTOMERDOCUMENT"));
                    retorno.setEmail(rs.getString("CUSTOMEREMAIL"));
                    retorno.setFirstName(rs.getString("CUSTOMERFIRSTNAME"));
                    retorno.setLastName(rs.getString("CUSTOMERLASTNAME"));
                    retorno.setLastUserCreditCardId(rs.getLong("LASTUSEDCREDITCARDID"));
                    retorno.setLastUserAddressId(rs.getLong("LASTUSEDADDRESSID"));
                    retorno.setPassword(rs.getString("CUSTOMERPASSWORD"));
                    retorno.setType(rs.getInt("CUSTOMERTYPE"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public Customer getByCustomer(long idCustomer) throws Exception {
        Customer retorno = null;

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_CUSTOMER);
            stmt.setLong(1, idCustomer);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                if (rs.next()) {
                    retorno = new Customer();
                    retorno.setId(rs.getLong("CUSTOMERID"));
                    retorno.setDocument(rs.getString("CUSTOMERDOCUMENT"));
                    retorno.setEmail(rs.getString("CUSTOMEREMAIL"));
                    retorno.setFirstName(rs.getString("CUSTOMERFIRSTNAME"));
                    retorno.setLastName(rs.getString("CUSTOMERLASTNAME"));
                    retorno.setLastUserCreditCardId(rs.getLong("LASTUSEDCREDITCARDID"));
                    retorno.setLastUserAddressId(rs.getLong("LASTUSEDADDRESSID"));
                    retorno.setPassword(rs.getString("CUSTOMERPASSWORD"));
                    retorno.setType(rs.getInt("CUSTOMERTYPE"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public long CreateCustomerAddress(Address adddress) throws Exception {
        long retorno = 0;
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(CREATE_ADDRESS);

            stmt.setLong(1, adddress.getCityId());
            stmt.setLong(2, adddress.getCustomerId());
            stmt.setString(3, adddress.getStreet());
            stmt.setInt(4, adddress.getType());
            stmt.setString(5, adddress.getTown());
            stmt.setString(6, adddress.getPhoneNumber());
            stmt.registerOutParameter(7, OracleTypes.INTEGER);
            stmt.execute();

            retorno = stmt.getLong(7);

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public void UpdateCustomerAddress(Address adddress) throws Exception {

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(UPDATE_ADDRESS);

            stmt.setLong(1, adddress.getId());
            stmt.setLong(2, adddress.getCityId());
            stmt.setLong(3, adddress.getCustomerId());
            stmt.setString(4, adddress.getStreet());
            stmt.setInt(5, adddress.getType());
            stmt.setString(6, adddress.getTown());
            stmt.setString(7, adddress.getPhoneNumber());

            stmt.execute();
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return;
    }

    public List<Address> getCustomerAddress(long idCustomer) throws Exception {
        List<Address> retorno = new ArrayList<Address>();

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_ADDRESS_CUSTOMER);
            stmt.setLong(1, idCustomer);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {
                    Address item = new Address();
                    item.setId(rs.getLong("ADDRESSID"));
                    item.setCityId(rs.getLong("CITYID"));
                    item.setCustomerId(rs.getLong("CUSTOMERID"));
                    item.setPhoneNumber(rs.getString("ADDRESSPHONENUMBER"));
                    item.setStreet(rs.getString("ADDRESSSTREET"));
                    item.setTown(rs.getString("ADDRESSTOWN"));
                    item.setType(rs.getInt("ADDRESSTYPE"));

                    retorno.add(item);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public long CreateCustomerCreditCard(CreditCard creditCard) throws Exception {
        long retorno = 0;
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(CREATE_CREDITCARD);

            stmt.setLong(1, creditCard.getCustomerId());
            stmt.setInt(2, creditCard.getType());
            stmt.setString(3, creditCard.getNumber());
            stmt.setInt(4, creditCard.getExpirationMonth());
            stmt.setInt(5, creditCard.getExpirationYear());
            stmt.setString(6, creditCard.getVerificationNumber());
            stmt.setInt(7, creditCard.isSaveCreditCardNumber() ? 1 : 0);
            stmt.registerOutParameter(8, OracleTypes.INTEGER);
            stmt.execute();

            retorno = stmt.getLong(8);

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public void UpdateCustomerCreditCard(CreditCard creditCard) throws Exception {

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(UPDATE_CREDITCARD);

            stmt.setLong(1, creditCard.getId());
            stmt.setInt(2, creditCard.getType());
            stmt.setString(3, creditCard.getNumber());
            stmt.setInt(4, creditCard.getExpirationMonth());
            stmt.setInt(5, creditCard.getExpirationYear());
            stmt.setString(6, creditCard.getVerificationNumber());
            stmt.setInt(7, creditCard.isSaveCreditCardNumber() ? 1 : 0);

            stmt.execute();

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return;
    }

    public List<CreditCard> getCustomerCreditCard(long idCustomer) throws Exception {
        List<CreditCard> retorno = new ArrayList<CreditCard>();

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_CREDITCARD_CUSTOMER);
            stmt.setLong(1, idCustomer);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {
                    CreditCard item = new CreditCard();
                    item.setCustomerId(idCustomer);
                    item.setId(rs.getLong("CREDITCARDID"));
                    item.setType(rs.getInt("CREDITCARDTYPE"));
                    item.setNumber(rs.getString("CREDITCARDNUMBER"));
                    item.setExpirationMonth(rs.getInt("CREDITCARDEXPIRATIONMONTH"));
                    item.setExpirationYear(rs.getInt("CREDITCARDEXPIRATIONYEAR"));
                    item.setVerificationNumber(rs.getString("CREDITCARDVERIFICATIONNUMBER"));
                    item.setSaveCreditCardNumber(rs.getInt("SAVECREDITCARDNUMBER") == 1);

                    retorno.add(item);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public CreditCard getCustomerCreditCardId(long idCreditCard) throws Exception {
        CreditCard retorno = new CreditCard();

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_CREDITCARD);
            stmt.setLong(1, idCreditCard);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                if (rs.next()) {
                    CreditCard item = new CreditCard();
                    item.setCustomerId(rs.getLong("CUSTOMERID"));
                    item.setId(rs.getLong("CREDITCARDID"));
                    item.setType(rs.getInt("CREDITCARDTYPE"));
                    item.setNumber(rs.getString("CREDITCARDNUMBER"));
                    item.setExpirationMonth(rs.getInt("CREDITCARDEXPIRATIONMONTH"));
                    item.setExpirationYear(rs.getInt("CREDITCARDEXPIRATIONYEAR"));
                    item.setVerificationNumber(rs.getString("CREDITCARDVERIFICATIONNUMBER"));
                    item.setSaveCreditCardNumber(rs.getInt("SAVECREDITCARDNUMBER") == 1);

                    retorno = item;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public ElementListPage<Customer> getCustomersByProduct(long productId, int page) throws Exception {
        ElementListPage<Customer> customerList = new ElementListPage<Customer>();
        customerList.setElementList(new ArrayList<Customer>());

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_CUSTOMERS_BY_PRODUCT);

            stmt.setLong(1, productId);
            stmt.setInt(2, page);
            stmt.setInt(3, 10);
            stmt.registerOutParameter(4, OracleTypes.INTEGER);
            stmt.registerOutParameter(5, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(5);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Customer retorno = new Customer();
                    retorno.setId(rs.getLong("CUSTOMERID"));
                    retorno.setDocument(rs.getString("CUSTOMERDOCUMENT"));
                    retorno.setEmail(rs.getString("CUSTOMEREMAIL"));
                    retorno.setFirstName(rs.getString("CUSTOMERFIRSTNAME"));
                    retorno.setLastName(rs.getString("CUSTOMERLASTNAME"));
                    retorno.setLastUserCreditCardId(rs.getLong("LASTUSEDCREDITCARDID"));
                    retorno.setLastUserAddressId(rs.getLong("LASTUSEDADDRESSID"));
                    retorno.setPassword("");
                    retorno.setType(rs.getInt("CUSTOMERTYPE"));

                    customerList.getElementList().add(retorno);
                }

                customerList.setPages(stmt.getInt(4));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return customerList;
    }

    public ElementListPage<Customer> getCustomersByBilling(Date startDate, Date endDate, int page) throws Exception {
        ElementListPage<Customer> customerList = new ElementListPage<Customer>();
        customerList.setElementList(new ArrayList<Customer>());

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        /*
        STARTDATE IN DATE,
        ENDDATE IN DATE,
        PAGE IN NUMBER,
        PAGESIZE IN NUMBER,
        TOTAL OUT NUMBER,
        RESPONSE OUT T_CURSOR)
         */

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_CUSTOMERS_BY_BILLING);

            stmt.setDate(1, new java.sql.Date(startDate.getTime()));
            stmt.setDate(2, new java.sql.Date(endDate.getTime()));
            stmt.setInt(3, page);
            stmt.setInt(4, 10);
            stmt.registerOutParameter(5, OracleTypes.INTEGER);
            stmt.registerOutParameter(6, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(6);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                while (rs.next()) {

                    Customer retorno = new Customer();
                    retorno.setId(rs.getLong("CUSTOMERID"));
                    retorno.setDocument(rs.getString("CUSTOMERDOCUMENT"));
                    retorno.setEmail(rs.getString("CUSTOMEREMAIL"));
                    retorno.setFirstName(rs.getString("CUSTOMERFIRSTNAME"));
                    retorno.setLastName(rs.getString("CUSTOMERLASTNAME"));
                    retorno.setLastUserCreditCardId(rs.getLong("LASTUSEDCREDITCARDID"));
                    retorno.setLastUserAddressId(rs.getLong("LASTUSEDADDRESSID"));
                    retorno.setPassword( String.valueOf(rs.getLong("TOTALBILLED")));
                    retorno.setType(rs.getInt("CUSTOMERTYPE"));

                    customerList.getElementList().add(retorno);
                }

                customerList.setPages(stmt.getInt(5));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return customerList;
    }
}
